# -*- coding: utf-8 -*-
import pandas as pd
from MySQLdb import connect, cursors
import configparser
import os
import matplotlib.pyplot as plt
import socket

last_update_date = '2020-08-27 00:00:00'
#%%
def construct_db_connections():
    """进行数据库的连接设置，使用的是sql库,输出是库表连接，用于连接数据库
    Returns
    -------
    jy_conn: TYPE
        DESCRIPTION.
    zj_conn: TYPE
        DESCRIPTION.

    """
    hostname = socket.gethostname()
    hostname_prefix = hostname.split('-')[0]
    is_server = hostname_prefix in ('dev00', 'online00', 'online01')

    if not is_server:
        """
        In local environment, you should put a configuration file named
        '.db_config.ini' in the current directory.
        The configuration file content format must be:
            [DEFAULT]
            user=<YOUR_DB_USERNAME>
            password=<YOUR_PASSWORD>
            schema=<YOUR_OWN_SCHEMA>

        NOTE: Please do NOT commit the configuration file to svn
        """
        pwd = os.getcwd()
        db_config_file_path = os.path.join(pwd, '.db_config.ini')
        assert os.path.exists(db_config_file_path), "db config file %s is not existed" % db_config_file_path
        parser = configparser.ConfigParser()
        parser.read(db_config_file_path)
        user = parser['DEFAULT']['user']
        password = parser['DEFAULT']['password']
        zj_schema = parser['DEFAULT']['schema']
        db_host = 'dev.zhijuninvest.com'
    else:
        user = 'cronjob'
        password = 'ZyYADQjvh68UjySZ'
        db_host = '172.17.0.1'
        zj_schema = 'zj_data'

    jy_conn = connect(host=db_host, port=3306, user=user, passwd=password,
                      db="JYDB", charset="utf8mb4", cursorclass=cursors.DictCursor)
    zj_conn = connect(host=db_host, port=3306, user=user, passwd=password,
                      db=zj_schema, charset="utf8mb4", cursorclass=cursors.DictCursor)

    return jy_conn, zj_conn

def get_mf_maximum_drawdown():
    query = """
        SELECT SecuCode, maximum_drawdown_12
        FROM zhijunfund.mf_maximum_drawdown_1
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_maximum_drawdown = pd.read_sql(query, zj_conn)
    return mf_maximum_drawdown

def get_mf_avg_drawdown():
    query = """
        SELECT SecuCode, avg_drawdown_12
        FROM zhijunfund.mf_avg_drawdown_1
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_avg_drawdown = pd.read_sql(query, zj_conn)
    return mf_avg_drawdown

def get_mf_annual_return():
    query = """
        SELECT SecuCode, annual_return_3, annual_return_12,annual_return_36
        FROM zhijunfund.mf_annual_return_1
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_annual_return = pd.read_sql(query, zj_conn)
    return mf_annual_return

def get_mf_ex_annual_return():
    query = """
        SELECT SecuCode, ex_annual_return_12, ex_annual_return_36
        FROM zhijunfund.mf_ex_annual_return_1
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_ex_annual_return = pd.read_sql(query, zj_conn)
    return mf_ex_annual_return


def get_mf_interval_return():
    query = """
        SELECT SecuCode, interval_return_3, interval_return_12, interval_return_36
        FROM zhijunfund.mf_interval_return_1
       WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_interval_return = pd.read_sql(query, zj_conn)
    return mf_interval_return


def get_open_fund_duration():
    query = """
        SELECT SecuCode, ChiNameAbbr, Duration
        FROM zhijunfund.fund_information
        WHERE ListedState = 1
    """
    open_fund_duration = pd.read_sql(query, zj_conn)
    return open_fund_duration

def get_mf_volatility():
    query = """
            SELECT SecuCode, volatility_12, volatility_24, volatility_36
            FROM zhijunfund.mf_volatility_1
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_volatility = pd.read_sql(query, zj_conn)
    return mf_volatility


def get_mf_ex_volatility():
    query = """
            SELECT SecuCode, ex_volatility_12, ex_volatility_24, ex_volatility_36
            FROM zhijunfund.mf_ex_volatility_1
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_ex_volatility = pd.read_sql(query, zj_conn)
    return mf_ex_volatility

def get_mf_var():
    query = """
            SELECT SecuCode, var_12, var_24, var_36
            FROM zhijunfund.mf_var_1
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_var = pd.read_sql(query, zj_conn)
    return mf_var

def get_mf_down_std():
    query = """
            SELECT SecuCode, down_std_12, down_std_24, down_std_36
            FROM zhijunfund.mf_down_std_1
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_down_std = pd.read_sql(query, zj_conn)
    return mf_down_std

if __name__ == '__main__':
    jy_conn, zj_conn = construct_db_connections()

    open_fund_duration = get_open_fund_duration()
    print("duration")
    print(open_fund_duration["Duration"].min())
    print(open_fund_duration["Duration"].mean())
    print(open_fund_duration["Duration"].median())
    print(open_fund_duration["Duration"].max())

    #print(open_fund_duration)
    #open_fund_duration.hist("Duration", bins = 50)
    #plt.show()

    '''
    hist1 = open_fund_duration[open_fund_duration["Duration"]>1000].hist("Duration", bins = 30)
    print(open_fund_duration[open_fund_duration["Duration"]>1000].count())
    plt.show()
    '''

    mf_maximum_drawdown = get_mf_maximum_drawdown()
    #print(mf_maximum_drawdown)
    print("maximum_drawdown_12")
    print(mf_maximum_drawdown["maximum_drawdown_12"].min())
    print(mf_maximum_drawdown["maximum_drawdown_12"].mean())
    print(mf_maximum_drawdown["maximum_drawdown_12"].median())
    print(mf_maximum_drawdown["maximum_drawdown_12"].max())
    #mf_maximum_drawdown.hist("maximum_drawdown_12", bins = 50)
    #plt.show()

    mf_avg_drawdown = get_mf_avg_drawdown()
    print("avg_drawdown_12")
    print(mf_avg_drawdown["avg_drawdown_12"].min())
    print(mf_avg_drawdown["avg_drawdown_12"].mean())
    print(mf_avg_drawdown["avg_drawdown_12"].median())
    print(mf_avg_drawdown["avg_drawdown_12"].max())
    #mf_avg_drawdown.hist("avg_drawdown_12", bins=100)
    #plt.xlim(0,2)
    #plt.show()

    mf_annual_return = get_mf_annual_return()
    #print(mf_annual_return)
    print("annual_return_12")
    print(mf_annual_return["annual_return_36"].min())
    print(mf_annual_return["annual_return_36"].mean())
    print(mf_annual_return["annual_return_36"].median())
    print(mf_annual_return["annual_return_36"].max())
    print("annual_return_36")
    print(mf_annual_return["annual_return_36"].min())
    print(mf_annual_return["annual_return_36"].mean())
    print(mf_annual_return["annual_return_36"].median())
    print(mf_annual_return["annual_return_36"].max())
    #mf_annual_return.hist("annual_return_12", bins = 50)
    #plt.show()

    mf_ex_annual_return = get_mf_ex_annual_return()
    print("ex_annual_return_12")
    print(mf_ex_annual_return["ex_annual_return_12"].min())
    print(mf_ex_annual_return["ex_annual_return_12"].mean())
    print(mf_ex_annual_return["ex_annual_return_12"].median())
    print(mf_ex_annual_return["ex_annual_return_12"].max())
    print("ex_annual_return_36")
    print(mf_ex_annual_return["ex_annual_return_36"].min())
    print(mf_ex_annual_return["ex_annual_return_36"].mean())
    print(mf_ex_annual_return["ex_annual_return_36"].median())
    print(mf_ex_annual_return["ex_annual_return_36"].max())
    #mf_ex_annual_return.hist("ex_annual_return_12", bins=50)
    #plt.show()
    #mf_ex_annual_return.hist("ex_annual_return_36", bins=50)
    #plt.show()

    '''
    mf_interval_return = get_mf_interval_return()
    mf_interval_return.hist("interval_return_36", bins=80)
    #plt.xlim(-0.2,0.6)
    plt.show()
    '''

    mf_volatility = get_mf_volatility()
    print("volatility_12")
    print(mf_volatility["volatility_12"].min())
    print(mf_volatility["volatility_12"].mean())
    print(mf_volatility["volatility_12"].median())
    print(mf_volatility["volatility_12"].max())
    print("volatility_24")
    print(mf_volatility["volatility_24"].min())
    print(mf_volatility["volatility_24"].mean())
    print(mf_volatility["volatility_24"].median())
    print(mf_volatility["volatility_24"].max())
    print("volatility_36")
    print(mf_volatility["volatility_36"].min())
    print(mf_volatility["volatility_36"].mean())
    print(mf_volatility["volatility_36"].median())
    print(mf_volatility["volatility_36"].max())

    '''
    mf_volatility.hist("volatility_12", bins = 80)
    plt.xlim(-0.1,0.6)
    plt.show()
    mf_volatility.hist("volatility_24", bins=80)
    plt.xlim(-0.1, 0.6)
    plt.show()
    mf_volatility.hist("volatility_36", bins=120)
    plt.xlim(-0.1, 0.6)
    plt.show()
    '''

    mf_var = get_mf_var()
    print("var_12")
    print(mf_var["var_12"].min())
    print(mf_var["var_12"].mean())
    print(mf_var["var_12"].median())
    print(mf_var["var_12"].max())
    print("var_24")
    print(mf_var["var_24"].min())
    print(mf_var["var_24"].mean())
    print(mf_var["var_24"].median())
    print(mf_var["var_24"].max())
    print("var_36")
    print(mf_var["var_36"].min())
    print(mf_var["var_36"].mean())
    print(mf_var["var_36"].median())
    print(mf_var["var_36"].max())
    '''
    mf_var.hist("var_12", bins = 80)
    plt.xlim(-0.10,0.02)
    plt.show()
    mf_var.hist("var_24", bins = 80)
    plt.xlim(-0.10, 0.02)
    plt.show()
    mf_var.hist("var_36", bins = 80)
    plt.xlim(-0.10, 0.02)
    plt.show()
    '''

    mf_ex_volatility = get_mf_ex_volatility()
    print("ex_volatility_12")
    print(mf_ex_volatility["ex_volatility_12"].min())
    print(mf_ex_volatility["ex_volatility_12"].mean())
    print(mf_ex_volatility["ex_volatility_12"].median())
    print(mf_ex_volatility["ex_volatility_12"].max())
    print("ex_volatility_24")
    print(mf_ex_volatility["ex_volatility_24"].min())
    print(mf_ex_volatility["ex_volatility_24"].mean())
    print(mf_ex_volatility["ex_volatility_24"].median())
    print(mf_ex_volatility["ex_volatility_24"].max())
    print("ex_volatility_36")
    print(mf_ex_volatility["ex_volatility_36"].min())
    print(mf_ex_volatility["ex_volatility_36"].mean())
    print(mf_ex_volatility["ex_volatility_36"].median())
    print(mf_ex_volatility["ex_volatility_36"].max())
    '''
    mf_ex_volatility.hist("ex_volatility_12", bins = 80)
    plt.xlim(-0.1,0.6)
    plt.show()
    mf_ex_volatility.hist("ex_volatility_24", bins=80)
    plt.xlim(-0.1, 0.6)
    plt.show()
    mf_ex_volatility.hist("ex_volatility_36", bins=120)
    plt.xlim(-0.1, 0.6)
    plt.show()
    '''

    mf_down_std = get_mf_down_std()
    print("down_std_12")
    print(mf_down_std["down_std_12"].min())
    print(mf_down_std["down_std_12"].mean())
    print(mf_down_std["down_std_12"].median())
    print(mf_down_std["down_std_12"].max())
    print("down_std_24")
    print(mf_down_std["down_std_24"].min())
    print(mf_down_std["down_std_24"].mean())
    print(mf_down_std["down_std_24"].median())
    print(mf_down_std["down_std_24"].max())
    print("down_std_36")
    print(mf_down_std["down_std_36"].min())
    print(mf_down_std["down_std_36"].mean())
    print(mf_down_std["down_std_36"].median())
    print(mf_down_std["down_std_36"].max())
    mf_down_std.hist("down_std_12", bins = 80)
    plt.xlim(0, 0.35)
    plt.show()
    mf_down_std.hist("down_std_24", bins = 80)
    plt.xlim(0, 0.35)
    plt.show()
    mf_down_std.hist("down_std_36", bins = 160)
    plt.xlim(0, 0.35)
    plt.show()



